﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.IO;
using FlexCel.XlsAdapter;
using FlexCel.Core;
using BUS;
using Gizmox.WebGUI.Forms;
using System.Drawing;

namespace TVC
{
    public class clsTransfer
    {
        public MainForm MyMainForm { get; set; }
        public clsTransfer(MainForm form)
        {
            MyMainForm = form;
        }
        public static DataTable PreviewExcelFile(DataTable dt, string filename, string fontCode, string importCode)
        {
            //DataTable dt = new DataTable();
            dt.PrimaryKey = null;
            foreach (DataColumn col in dt.Columns)
            {
                col.AllowDBNull = true;
            }
            if (File.Exists(filename))
            {
                XlsFile xlsFile = new XlsFile(filename, true);
                List<TXlsNamedRange> arrRange = new List<TXlsNamedRange>();

                int maxCol = 1;
                int maxRow = 1;

                GetDataRange(dt, fontCode, importCode, xlsFile);

                //}
                if (dt.Rows.Count > 0)
                    return dt;
                InitAdvance(dt, xlsFile, arrRange, ref maxCol, ref maxRow);
                if (arrRange.Count == 0)
                    return dt;
                GetAdvance(dt, fontCode, xlsFile, arrRange, maxCol, maxRow);
            }
            return dt;
        }

        private static void GetDataRange(DataTable dt, string fontCode, string importCode, ExcelFile xlsFile)
        {
            TXlsNamedRange range = xlsFile.GetNamedRange("data_" + importCode, 0);

            if (range == null)
            {
                range = xlsFile.GetNamedRange("data", 0);
            }
            if (range != null)
            {
                xlsFile.ActiveSheet = range.SheetIndex;
                for (int r = 1; r < range.RowCount; r++)
                {
                    DataRow newRow = dt.NewRow();
                    for (int c = 0; c < range.ColCount; c++)
                    {
                        if (xlsFile.GetCellValue(range.Top, range.Left + c) != null && dt.Columns.Contains(xlsFile.GetCellValue(range.Top, range.Left + c).ToString()))
                        {
                            GetValue(ref newRow, xlsFile.GetCellValue(range.Top, range.Left + c).ToString(), xlsFile, range.Top + r, range.Left + c, fontCode);
                        }
                    }
                    dt.Rows.Add(newRow);
                }

            }
        }
        public static DataSet PreviewExcelFile(DataSet ds, string filename, string fontCode, string importCode)
        {
            if (File.Exists(filename))
            {
                XlsFile xlsFile = new XlsFile(filename, true);
                foreach (DataTable dt in ds.Tables)
                {
                    //DataTable dt = new DataTable();
                    dt.PrimaryKey = null;
                    foreach (DataColumn col in dt.Columns)
                    {
                        col.AllowDBNull = true;
                    }

                    List<TXlsNamedRange> arrRange = new List<TXlsNamedRange>();

                    int maxCol = 1;
                    int maxRow = 1;
                    GetDataRange(dt, fontCode, importCode, xlsFile);


                    if (dt.Rows.Count > 0)
                        break;
                    InitAdvance(dt, xlsFile, arrRange, ref maxCol, ref maxRow);
                    if (arrRange.Count == 0)
                        break;
                    GetAdvance(dt, fontCode, xlsFile, arrRange, maxCol, maxRow);
                }
            }
            return ds;
        }

        public static DataSet PreviewExcelFile(DataSet ds, ExcelFile xlsFile, string fontCode, string importCode)
        {
            foreach (DataTable dt in ds.Tables)
            {
                //DataTable dt = new DataTable();
                dt.PrimaryKey = null;
                foreach (DataColumn col in dt.Columns)
                {
                    col.AllowDBNull = true;
                }

                List<TXlsNamedRange> arrRange = new List<TXlsNamedRange>();

                int maxCol = 1;
                int maxRow = 1;
                GetDataRange(dt, fontCode, importCode, xlsFile);
                if (dt.Rows.Count > 0)
                    break;
                InitAdvance(dt, xlsFile, arrRange, ref maxCol, ref maxRow);
                if (arrRange.Count == 0)
                    break;
                GetAdvance(dt, fontCode, xlsFile, arrRange, maxCol, maxRow);
            }
            return ds;
        }
        public static DataTable PreviewExcelFile(DataTable dt, ExcelFile xlsFile, string fontCode, string importCode)
        {
            //DataTable dt = new DataTable();
            dt.PrimaryKey = null;
            foreach (DataColumn col in dt.Columns)
            {
                col.AllowDBNull = true;
            }

            List<TXlsNamedRange> arrRange = new List<TXlsNamedRange>();

            int maxCol = 1;
            int maxRow = 1;
            GetDataRange(dt, fontCode, importCode, xlsFile);


            if (dt.Rows.Count > 0)
                return dt;
            InitAdvance(dt, xlsFile, arrRange, ref maxCol, ref maxRow);
            if (arrRange.Count == 0)
                return dt;
            GetAdvance(dt, fontCode, xlsFile, arrRange, maxCol, maxRow);

            return dt;
        }
        private static void GetAdvance(DataTable dt, string fontCode, ExcelFile xlsFile, List<TXlsNamedRange> arrRange, int maxCol, int maxRow)
        {
            for (int i = 0; i < maxCol; i++)
            {
                for (int j = 0; j < maxRow; j++)
                {
                    DataRow newRow = dt.NewRow();
                    bool flag = true;
                    for (int index = 0; index < arrRange.Count; index++)
                    {
                        xlsFile.ActiveSheet = arrRange[index].SheetIndex;
                        int top = Math.Min(j, arrRange[index].RowCount - 1) + arrRange[index].Top;
                        int left = Math.Min(i, arrRange[index].ColCount - 1) + arrRange[index].Left;
                        if (index == 0)
                        {
                            int atop = top;
                            int aleft = left;
                            if (!xlsFile.CellMergedBounds(top, left).IsOneCell)
                            {
                                atop = xlsFile.CellMergedBounds(top, left).Top;
                                aleft = xlsFile.CellMergedBounds(top, left).Left;
                            }
                            object dObject = GetObject(xlsFile.GetCellValue(atop, aleft), fontCode);
                            if (dObject == null || dObject.ToString() == "" || dObject == DBNull.Value)
                            {
                                flag = false;
                                break;
                            }
                            else
                            {
                                GetValue(ref newRow, GetFieldName(dt, arrRange[index].Name), xlsFile, top, left, fontCode);
                            }
                        }
                        else
                        {
                            GetValue(ref newRow, GetFieldName(dt, arrRange[index].Name), xlsFile, top, left, fontCode);
                        }

                    }
                    if (flag)
                    {
                        dt.Rows.Add(newRow);
                    }
                }
            }
        }

        private static string GetFieldName(DataTable dt, string p)
        {
            return p.Replace(String.Format("f__{0}_", dt.TableName), "").Replace(String.Format("d__{0}_", dt.TableName), "").Replace("f__", "").Replace("d__", "");
        }

        private static void InitAdvance(DataTable dt, ExcelFile xlsFile, List<TXlsNamedRange> arrRange, ref int maxCol, ref int maxRow)
        {
            for (int i = 0; i < xlsFile.NamedRangeCount; i++)
            {
                string name = xlsFile.GetNamedRange(i + 1).Name;
                if (name.Length > 3 && (name.Substring(0, 3) == "d__" || name.Substring(0, 3) == "f__"))
                {

                    if (name.Contains(String.Format("f__{0}_", dt.TableName)) || name.Contains(String.Format("d__{0}_", dt.TableName)))
                    {
                        string colName = name.Replace(String.Format("f__{0}_", dt.TableName), "").Replace(String.Format("d__{0}_", dt.TableName), "");
                        if (dt.Columns.Contains(colName))
                        {
                            TXlsNamedRange range = xlsFile.GetNamedRange(i + 1);
                            //if (maxCol < range.ColCount) maxCol = range.ColCount;
                            //if (maxRow < range.RowCount) maxRow = range.RowCount;

                            for (int j = 0; j < arrRange.Count; j++)
                            {
                                if (arrRange[j].Name == "f__" + colName || arrRange[j].Name == "d__" + colName)
                                {
                                    arrRange.RemoveAt(j);
                                    break;
                                }
                            }
                            if (name.Substring(0, 3) == "d__")
                            {
                                arrRange.Insert(0, range);
                            }
                            else
                            {
                                //if (maxCol < range.ColCount) maxCol = range.ColCount;
                                arrRange.Add(range);
                            }
                        }
                    }
                    else
                    {
                        if (dt.Columns.Contains(name.Substring(3)))
                        {
                            TXlsNamedRange range = xlsFile.GetNamedRange(i + 1);
                            //if (maxCol < range.ColCount) maxCol = range.ColCount;
                            //if (maxRow < range.RowCount) maxRow = range.RowCount;
                            bool flag = true;
                            for (int j = 0; j < arrRange.Count; j++)
                            {
                                if (arrRange[j].Name == String.Format("f__{0}_{1}", dt.TableName, name.Substring(3)) || arrRange[j].Name == String.Format("d__{0}_{1}", dt.TableName, name.Substring(3)))
                                {
                                    flag = false;
                                    break;
                                }
                            }
                            if (flag)
                            {
                                if (name.Substring(0, 3) == "d__")
                                {
                                    arrRange.Insert(0, range);
                                }
                                else
                                {
                                    //if (maxCol < range.ColCount) maxCol = range.ColCount;
                                    arrRange.Add(range);
                                }
                            }
                        }
                    }
                }
            }
            foreach (TXlsNamedRange x in arrRange)
            {
                if (maxCol < x.ColCount) maxCol = x.ColCount;
                if (maxRow < x.RowCount) maxRow = x.RowCount;
            }
        }

        private static void GetValue(ref DataRow newRow, string fieldName, ExcelFile xlsFile, int top, int left, string fontCode)
        {
            int atop = top;
            int aleft = left;
            try
            {
                if (!xlsFile.CellMergedBounds(atop, aleft).IsOneCell)
                {
                    atop = xlsFile.CellMergedBounds(atop, aleft).Top;
                    aleft = xlsFile.CellMergedBounds(atop, aleft).Left;
                }
                if (newRow.Table.Columns[fieldName].DataType == typeof(DateTime))
                    newRow[fieldName] = GetDateTimeObj(xlsFile, atop, aleft);
                else
                    newRow[fieldName] = GetObject(xlsFile.GetCellValue(atop, aleft), fontCode);
            }
            catch (Exception ex)
            {
                throw new Exception(String.Format(@"Erro {0}!R{1}C{2} : {3}", xlsFile.ActiveSheetByName, top, left, ex.Message));
            }
        }

        private static object GetDateTimeObj(ExcelFile xlsFile, int top, int left)
        {
            if (xlsFile.GetCellVisibleFormatDef(top, left).Format != "")
            {
                if (xlsFile.GetCellValue(top, left) != null || xlsFile.GetCellValue(top, left).ToString() == "" || xlsFile.GetCellValue(top, left).ToString() == "NULL")
                    return FlxDateTime.FromOADate((double)xlsFile.GetCellValue(top, left), false);
                else return DBNull.Value;
            }
            else
            {
                Object obj = xlsFile.GetCellValue(top, left);
                if (obj is double)
                {
                    int sunDate = Convert.ToInt32(obj);
                    int year = sunDate / 10000;
                    int month = (sunDate - year * 10000) / 100;
                    int day = sunDate - year * 10000 - month * 100;
                    return new DateTime(year, month, day);
                }
                else
                    return DBNull.Value;
            }
        }
        private static object GetObject(object p, string fontCode)
        {
            //object p = null;
            if (p == null || p.ToString().Trim().TrimStart() == "")
                return DBNull.Value;
            if (p is String)
            {
                if (fontCode == "" || fontCode == "None" || fontCode == "Unicode")
                {
                    //if (p is string)
                    return p.ToString().Trim();
                    //return p;
                }
                else
                {

                    if (fontCode == "TVCN3")
                        return VNConvertor.ConvertTCVN3ToUnicode(p.ToString().Trim().TrimStart());
                    else if (fontCode == "VNI")
                        return VNConvertor.ConvertVNI2Unicode(p.ToString().Trim().TrimStart());
                    //else 
                    //    return 
                }

            }
            else if (p is TFormula)
            {
                TFormula f = p as TFormula;
                if (f.Result is String)
                {
                    if (fontCode == "" || fontCode == "None" || fontCode == "Unicode")
                    {
                        //if (p is string)
                        return p.ToString().Trim();
                        //return p;
                    }
                    else
                    {

                        if (fontCode == "TVCN3")
                            return VNConvertor.ConvertTCVN3ToUnicode(p.ToString().Trim().TrimStart());
                        else if (fontCode == "VNI")
                            return VNConvertor.ConvertVNI2Unicode(p.ToString().Trim().TrimStart());
                        //else 
                        //    return 
                    }

                }
                return f.Result;
            }
            return p;
        }
        public void ValidateDGV(DataGridView dgvList, ErrorProvider errorProvider1)
        {
            string sErr = "";
            DataTable dt = dgvList.DataSource as DataTable;
            if (dt != null)
            {
                DataSet dsValidate = new DataSet();
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    DataRow row = dt.Rows[j];
                    bool flag = false;
                    string msg = "";
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        if (row[i].ToString() == "")
                            flag = true;
                        else
                        {
                            if (dt.Columns[i].ExtendedProperties.ContainsKey("lookup"))
                            {
                                //CategoryControl cateCtr = new CategoryControl();
                                //string lookup = dt.Columns[i].ExtendedProperties["lookup"].ToString();
                                //if (cateCtr.IsExist(MyMainForm.DB, lookup))
                                //{
                                //    DTO.CategoryInfo cateinfo = cateCtr.Get(MyMainForm.DB, lookup, ref sErr);

                                //}
                                //else
                                //{
                                //    flag = MyMainForm.IsContainsKey(lookup, row[i].ToString());
                                //}
                                string lookup = dt.Columns[i].ExtendedProperties["lookup"].ToString();
                                string code = "";
                                DataTable adt = null;
                                if (!dsValidate.Tables.Contains(lookup))
                                    adt = MyMainForm.GetDataSource(lookup, ref code, ref sErr);
                                else
                                    adt = dsValidate.Tables[lookup];
                                flag = false;
                                if (adt.Columns.Contains("Code"))
                                {
                                    foreach (DataRow vrow in adt.Rows)
                                    {
                                        if (vrow["Code"].ToString() == row[i].ToString())
                                        {
                                            flag = true;
                                            break;
                                        }
                                    }
                                }
                                else if (adt.Columns.Contains(dt.Columns[i].ColumnName))
                                {
                                    foreach (DataRow vrow in adt.Rows)
                                    {

                                        if (vrow[dt.Columns[i].ColumnName].ToString() == row[i].ToString())
                                        {
                                            flag = true;
                                            break;
                                        }
                                    }
                                }
                                else if (adt.PrimaryKey != null && adt.PrimaryKey.Length > 1)
                                {
                                    foreach (DataRow vrow in adt.Rows)
                                    {

                                        if (vrow[adt.PrimaryKey[0].ColumnName].ToString() == row[i].ToString())
                                        {
                                            flag = true;
                                            break;
                                        }
                                    }
                                }
                                if (!dsValidate.Tables.Contains(lookup))
                                {
                                    DataTable dtValid = adt.Copy();
                                    dtValid.TableName = lookup;
                                    dsValidate.Tables.Add(dtValid);
                                }
                                if (flag == false)
                                {
                                    msg = String.Format("A '{0}' is not in {1} list!", row[i], lookup);
                                    break;
                                }
                            }
                            else flag = true;
                        }

                    }
                    if (flag == false)
                    {
                        dgvList.Rows[j].ErrorText = msg;
                        dgvList.Rows[j].DefaultCellStyle.ForeColor = Color.Red;
                        if (!errorProvider1.GetError(dgvList).Contains(msg))
                            errorProvider1.SetError(dgvList, String.Format("{0}\n{1}", errorProvider1.GetError(dgvList), msg));
                    }
                }
            }
        }
    }
}
